"""
数据库交互
"""
from common.sql_helper import get_pool
import datetime


def get_city_list_model():
    sql_str = f"""select * from flask_city"""
    city_list = get_pool("DB_API_AUTO").fetchall(sql_str)
    return city_list


def get_area_list_model(city_id):
    sql_str = f"""select id, area_name from flask_area where city_id = %s"""
    area_list = get_pool("DB_API_AUTO").fetchall(sql_str, city_id)
    return area_list


def get_city_name_model(city_id):
    sql_str = f"""select city_name from flask_city where id = %s"""
    city_name = get_pool("DB_API_AUTO").fetchone(sql_str, city_id).get('city_name')
    return city_name


def get_area_name_model(area_id):
    sql_str = f"""select area_name from flask_area where id = %s"""
    area_name = get_pool("DB_API_AUTO").fetchone(sql_str, area_id).get('area_name')
    return area_name


def add_work_order_model(json_dict):
    get_pool("DB_API_AUTO").insert_helper("flask_work_order", json_dict)


def get_work_order_model(where_dict, limit_dict):
    # 查询记录数
    sql_count_str = """select count(*) as num from flask_work_order"""
    count = get_pool("DB_API_AUTO").select_helper(sql_count_str, where_dict)[0]["num"]
    # 组合条件查询
    sql_str = """select * from flask_work_order order by created_at desc"""
    result = get_pool("DB_API_AUTO").select_helper(sql_str, where_dict, limit_dict)

    # 格式化 mysql datetime 日期格式
    if result:
        for data in result:
            data["listen_time"] = datetime.datetime.strftime(data["listen_time"], "%Y-%m-%d %H:%M:%S")
            data["created_at"] = datetime.datetime.strftime(data["created_at"], "%Y-%m-%d %H:%M:%S")
            data["updated_at"] = datetime.datetime.strftime(data["updated_at"], "%Y-%m-%d %H:%M:%S")
    return count, result


def del_work_order_model(_id):
    result = get_pool("DB_API_AUTO").fetchone("select * from flask_work_order where id=%s", _id)
    if result:
        get_pool("DB_API_AUTO").execute_dml("delete from flask_work_order where id=%s", _id)
        return True


def put_work_order_model(json_dict):
    # 更新前判断是否存在
    result = get_pool("DB_API_AUTO").fetchone("select * from flask_work_order where id=%s", json_dict["id"])
    if result:
        sql_str = "update flask_work_order set phone_number=%s, remark=%s where id=%s"
        get_pool("DB_API_AUTO").execute_dml(
            sql_str, json_dict["phone_number"], json_dict["remark"], json_dict["id"])
        return True
